package com.examsys.dao;

import java.sql.Timestamp;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.examsys.po.ExamDetail;
import com.examsys.po.ExamMain;
import com.examsys.po.Paper;
import com.examsys.po.PaperDetail;
import com.examsys.po.PaperSection;
import com.examsys.po.Question;
import com.examsys.po.Users;

/**
 * 考生答题卡数据访问层实现类
 * @author edu-1
 *
 */
public class ExamMainDaoImpl extends AbstractBaseDao<ExamMain, Integer> implements ExamMainDao {

	/**
	 * 添加考生答题卡
	 */
	@Override
	public void add(ExamMain obj) throws Exception {
		//构造插入语句
		String sql="Insert into EXAM_MAIN (ID,USER_ID,PAPER_ID,START_TIME,END_TIME,SCORE,IP,STATUS,REMARK) values (EXAM_MAIN_ID_SEQ.nextval,?,?,?,?,?,?,?,?)";
		this.execute(sql, new Object[]{obj.getUsers().getId(),obj.getPaper().getId(),obj.getStart_time(),obj.getEnd_time(),obj.getScore(),obj.getIp(),obj.getStatus(),obj.getRemark()});
	}

	/**
	 * 更新考生答题卡
	 */
	@Override
	public void update(ExamMain obj) throws Exception {
		//构造更新语句
		String sql="UPDATE EXAM_MAIN SET USER_ID=?,PAPER_ID=?,START_TIME=?,END_TIME=?,SCORE=?,IP=?,STATUS=?,REMARK=? WHERE ID=?";
		this.execute(sql, new Object[]{obj.getUsers().getId(),obj.getPaper().getId(),obj.getStart_time(),obj.getEnd_time(),obj.getScore(),obj.getIp(),obj.getStatus(),obj.getRemark(),obj.getId()});
	}

	/**
	 * 删除考生答题卡
	 */
	@Override
	public void delete(Integer id) throws Exception {
		//构建删除语句
		String sql="DELETE FROM EXAM_MAIN WHERE ID=?";
		this.execute(sql, new Object[]{id});
	}

	/**
	 * 获取考生答题卡
	 * @param id 编号
	 */
	@Override
	public ExamMain get(Integer id) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.USER_ID,a.PAPER_ID,a.START_TIME,a.END_TIME,a.SCORE,a.IP,a.STATUS,a.REMARK,b.PAPER_NAME,c.USER_NAME,c.USER_NO,c.REAL_NAME FROM EXAM_MAIN a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USERS c ON a.USER_ID=c.ID WHERE a.ID=?";
		Map m = this.uniqueQuery(sql, new Object[]{id});//调用父类的查询方法拿数据
		
		Integer idd = (Integer)m.get("ID");//编号
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp start_time = (Timestamp)m.get("START_TIME");//开始考试时间
		Timestamp start_time2 = new Timestamp(start_time.getTime());//需要转换一下时间
		
		//由于数据返回的日期类型是Timestamp, 得强转
		Timestamp end_time = (Timestamp)m.get("END_TIME");//结束考试时间
		Timestamp end_time2 = new Timestamp(end_time.getTime());//需要转换一下时间
				
		Integer score = (Integer)m.get("SCORE");//总得分
		String ip = (String)m.get("IP");//登录进来的IP
		String status = (String)m.get("STATUS");//考试状态
		String remark = (String)m.get("REMARK");//备注
		
		Integer user_id = (Integer)m.get("USER_ID");//编号
		String user_name = (String)m.get("USER_NAME");//会员名
		String real_name = (String)m.get("REAL_NAME");//真实姓名
		String user_no = (String)m.get("USER_NO");//学号
		
		Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
		String paper_name = (String)m.get("PAPER_NAME");//试卷名称
		
		ExamMain examMain=new ExamMain();//创建实体类对象
		examMain.setId(idd);
		examMain.setStart_time(start_time2);
		examMain.setEnd_time(end_time2);
		examMain.setIp(ip);
		examMain.setScore(score);
		examMain.setStatus(status);
		examMain.setRemark(remark);
		
		Users users=new Users();//创建实体类对象
		users.setId(user_id);
		users.setUser_name(user_name);
		users.setUser_no(user_no);
		users.setReal_name(real_name);
		
		Paper paper=new Paper();//创建实体类对象
		paper.setId(paper_id);
		paper.setPaper_name(paper_name);
		
		getPaper(paper_id, paper);//设置试卷章节及期明细信息
		
		examMain.setUsers(users);//设置关联用户对象
		examMain.setPaper(paper);//设置关联试卷对象
		
		List<ExamDetail> examDetailList = getExamDetailList(examMain);
		examMain.setExamDetails(examDetailList);//设置关联答题卡明细对象
		
		return examMain;
	}

	private void getPaper(Integer paper_id, Paper paper) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.PAPER_ID,a.SECTION_NAME,a.PER_SCORE,a.REMARK,b.PAPER_NAME FROM PAPER_SECTION a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID WHERE a.PAPER_ID=?";
		List<Map> resultList = this.query(sql, new Object[]{paper_id});//调用父类的查询方法拿数据
		List<PaperSection> paperSectionList=new ArrayList<PaperSection>();//存放PaperSection类型对象的集合
		
		for(Map mp:resultList){
			Integer idd1 = (Integer)mp.get("ID");//编号
			Integer per_score = (Integer)mp.get("PER_SCORE");//本章节的分数
			String section_name = (String)mp.get("SECTION_NAME");//章节名称
			String remark1 = (String)mp.get("REMARK");//备注
			
			PaperSection paperSection=new PaperSection();//创建实体类对象
			paperSection.setId(idd1);
			paperSection.setPer_score(per_score);
			paperSection.setSection_name(section_name);
			paperSection.setRemark(remark1);
			
			paperSection.setPaper(paper);//设置关联对象
			paperSectionList.add(paperSection);
			
			List<PaperDetail> paperDetailList = getPaperDetail(idd1, paper);
			paperSection.setPaperDetails(paperDetailList);//设置关联对象
		}
		
		paper.setPaperSections(paperSectionList);//关联章节
	}

	/**
	 * 获得章节中的明细
	 * @param paper_section_id
	 * @param paper
	 * @return
	 * @throws Exception
	 */
	private List<PaperDetail> getPaperDetail(Integer paper_section_id, Paper paper) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.PAPER_ID,a.PAPER_SECTION_ID,a.QUESTION_ID,a.SCORE,a.PORDER,b.PAPER_NAME,c.SECTION_NAME,d.CONTENT FROM PAPER_DETAIL a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN PAPER_SECTION c ON a.PAPER_SECTION_ID=c.ID LEFT JOIN QUESTION d ON a.QUESTION_ID=d.ID WHERE a.PAPER_SECTION_ID=?";
		
		List<Map> resultList = this.query(sql, new Object[]{paper_section_id});//调用父类的查询方法拿数据
		List<PaperDetail> paperDetailList =new ArrayList<PaperDetail> ();//存放ExamDetail类型对象的集合
		
		for(Map m3:resultList){
			Integer id1 =(Integer)m3.get("ID");//编号
			Double score1 =(Double)m3.get("SCORE");//本题分数
			Integer porder =(Integer)m3.get("PORDER");//排序号
			
			String  section_name =(String)m3.get("SECTION_NAME");//章节名称
			
			Integer question_id =(Integer)m3.get("QUESTION_ID");//试题编号
			String  content =(String)m3.get("CONTENT");//试题题干
			
			//创建试卷明细实体类对象
			PaperDetail paperDetail=new PaperDetail();
			paperDetail.setId(id1);//编号
			paperDetail.setScore(score1);//本题分数
			paperDetail.setPorder(porder);//排序号
			
			paperDetail.setPaper(paper);//设置关联对象
			
			//创建章节实体类对象
			PaperSection paperSection=new PaperSection();
			paperSection.setId(paper_section_id);//章节编号
			paperSection.setSection_name(section_name);//章节名称
			paperDetail.setPaperSection(paperSection);//设置关联对象
			
			//创建试题实体类对象
			Question question=new Question();
			question.setId(question_id);//试题编号
			question.setContent(content);//试题题干
			paperDetail.setQuestion(question);//设置关联对象
		}
		return paperDetailList;
	}

	/**
	 * 获取试卷明细信息
	 * @param examMain
	 * @return
	 * @throws Exception
	 */
	private List<ExamDetail> getExamDetailList(ExamMain examMain) throws Exception {
		String sql;
		//构建查询语句,查找此试卷的明细信息
		sql="SELECT a.ID,a.MAIN_ID,a.QUESTION_ID,a.ANSWER,a.SCORE,a.STATUS,a.REMARK,b.CONTENT,b.SKEY FROM EXAM_DETAIL a LEFT JOIN exam_main b ON a.main_id=b.ID LEFT JOIN QUESTION c ON a.QUESTION_ID=c.ID WHERE a.MAIN_ID=?";
		List<Map> resultList = this.query(sql, new Object[]{examMain.getId()});//调用父类的查询方法拿数据
		List<ExamDetail> examDetailList =new ArrayList<ExamDetail> ();//存放ExamDetail类型对象的集合
		
		for(Map m1:resultList){
			Integer idd1 = (Integer)m1.get("ID");//编号
			Integer score1 = (Integer)m1.get("SCORE");//总得分
			String answer = (String)m1.get("ANSWER");//考生答题内容
			String status1 = (String)m1.get("STATUS");//考试状态
			String remark1 = (String)m1.get("REMARK");//备注
			
			Integer question_id = (Integer)m1.get("QUESTION_ID");//题目编号
			String content = (String)m1.get("CONTENT");//题干内容
			String skey = (String)m1.get("SKEY");//标准答案
			
			ExamDetail examDetail=new ExamDetail();//创建实体类对象
			examDetail.setId(idd1);
			examDetail.setScore(score1);
			examDetail.setAnswer(answer);
			examDetail.setStatus(status1);
			examDetail.setRemark(remark1);
			
			Question question=new Question();//创建实体类对象
			question.setId(question_id);
			question.setContent(content);
			question.setSkey(skey);
			
			examDetail.setQuestion(question);//设置关联对象
			examDetail.setExamMain(examMain);//设置关联对象
			examDetailList.add(examDetail);
		}
		return examDetailList;
	}

	/**
	 * 获取所有考生答题卡记录
	 */
	@Override
	public List<ExamMain> getList() throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.USER_ID,a.PAPER_ID,a.START_TIME,a.END_TIME,a.SCORE,a.IP,a.STATUS,a.REMARK,b.PAPER_NAME,c.USER_NAME,c.USER_NO,c.REAL_NAME FROM EXAM_MAIN a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USERS c ON a.USER_ID=c.ID";
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<ExamMain> list=new ArrayList<ExamMain>();//存放ExamMain类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp start_time = (Timestamp)m.get("START_TIME");//开始考试时间
			Timestamp start_time2 = new Timestamp(start_time.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp end_time = (Timestamp)m.get("END_TIME");//结束考试时间
			Timestamp end_time2 = new Timestamp(end_time.getTime());//需要转换一下时间
					
			Integer score = (Integer)m.get("SCORE");//总得分
			String ip = (String)m.get("IP");//登录进来的IP
			String status = (String)m.get("STATUS");//考试状态
			String remark = (String)m.get("REMARK");//备注
			
			Integer user_id = (Integer)m.get("USER_ID");//编号
			String user_name = (String)m.get("USER_NAME");//会员名
			String real_name = (String)m.get("REAL_NAME");//真实姓名
			String user_no = (String)m.get("USER_NO");//学号
			
			Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
			String paper_name = (String)m.get("PAPER_NAME");//试卷名称
			
			ExamMain examMain=new ExamMain();//创建实体类对象
			examMain.setId(idd);
			examMain.setStart_time(start_time2);
			examMain.setEnd_time(end_time2);
			examMain.setIp(ip);
			examMain.setScore(score);
			examMain.setStatus(status);
			examMain.setRemark(remark);
			
			Users users=new Users();//创建实体类对象
			users.setId(user_id);
			users.setUser_name(user_name);
			users.setUser_no(user_no);
			users.setReal_name(real_name);
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(paper_id);
			paper.setPaper_name(paper_name);
			
			examMain.setUsers(users);//设置关联对象
			examMain.setPaper(paper);//设置关联对象
			
			List<ExamDetail> examDetailList = getExamDetailList(examMain);
			examMain.setExamDetails(examDetailList);//设置关联对象
			
			list.add(examMain);
		}
				
		return list;
	}

	/**
	 * 带条件获取考生答题卡
	 */
	@Override
	public List<ExamMain> getList(ExamMain obj) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.USER_ID,a.PAPER_ID,a.START_TIME,a.END_TIME,a.SCORE,a.IP,a.STATUS,a.REMARK,b.PAPER_NAME,c.USER_NAME,c.USER_NO,c.REAL_NAME FROM EXAM_MAIN a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USERS c ON a.USER_ID=c.ID WHERE 1=1";
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<ExamMain> list=new ArrayList<ExamMain>();//存放ExamMain类型对象的集合
		
		if(obj!=null){//条件构造
			
			if(obj.getId()!=null&&obj.getId()!=0){
				sql+=" AND a.ID="+obj.getId();
			}
			
			if(obj.getUsers()!=null&&obj.getUsers().getId()!=null
					&&obj.getUsers().getId()!=0){
				sql+=" AND a.USER_ID="+obj.getUsers().getId();
			}
			
			if(obj.getUsers()!=null&&obj.getUsers().getUser_name()!=null
					&&!"".equals(obj.getUsers().getUser_name())){
				sql+=" AND c.USER_NAME='"+obj.getUsers().getUser_name()+"'";
			}
			
			if(obj.getUsers()!=null&&obj.getUsers().getUser_no()!=null
					&&!"".equals(obj.getUsers().getUser_no())){
				sql+=" AND c.USER_NO='"+obj.getUsers().getUser_no()+"'";
			}
			
			if(obj.getUsers()!=null&&obj.getUsers().getReal_name()!=null
					&&!"".equals(obj.getUsers().getReal_name())){
				sql+=" AND c.Real_NAME='"+obj.getUsers().getReal_name()+"'";
			}
			
			if(obj.getPaper()!=null&&obj.getPaper().getId()!=null
					&&obj.getPaper().getId()!=0){
				sql+=" AND b.PAPER_ID="+obj.getPaper().getId();
			}
			
			if(obj.getPaper()!=null&&obj.getPaper().getPaper_name()!=null
					&&!"".equals(obj.getPaper().getPaper_name())){
				sql+=" AND b.PAPER_NAME='"+obj.getPaper().getPaper_name()+"'";
			}
			
		}
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp start_time = (Timestamp)m.get("START_TIME");//开始考试时间
			Timestamp start_time2 = new Timestamp(start_time.getTime());//需要转换一下时间
			
			//由于数据返回的日期类型是Timestamp, 得强转
			Timestamp end_time = (Timestamp)m.get("END_TIME");//结束考试时间
			Timestamp end_time2 = new Timestamp(end_time.getTime());//需要转换一下时间
					
			Integer score = (Integer)m.get("SCORE");//总得分
			String ip = (String)m.get("IP");//登录进来的IP
			String status = (String)m.get("STATUS");//考试状态
			String remark = (String)m.get("REMARK");//备注
			
			Integer user_id = (Integer)m.get("USER_ID");//编号
			String user_name = (String)m.get("USER_NAME");//会员名
			String real_name = (String)m.get("REAL_NAME");//真实姓名
			String user_no = (String)m.get("USER_NO");//学号
			
			Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
			String paper_name = (String)m.get("PAPER_NAME");//试卷名称
			
			ExamMain examMain=new ExamMain();//创建实体类对象
			examMain.setId(idd);
			examMain.setStart_time(start_time2);
			examMain.setEnd_time(end_time2);
			examMain.setIp(ip);
			examMain.setScore(score);
			examMain.setStatus(status);
			examMain.setRemark(remark);
			
			Users users=new Users();//创建实体类对象
			users.setId(user_id);
			users.setUser_name(user_name);
			users.setUser_no(user_no);
			users.setReal_name(real_name);
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(paper_id);
			paper.setPaper_name(paper_name);
			
			examMain.setUsers(users);//设置关联对象
			examMain.setPaper(paper);//设置关联对象
			
			List<ExamDetail> examDetailList = getExamDetailList(examMain);
			examMain.setExamDetails(examDetailList);//设置关联对象
			
			list.add(examMain);
		}
		return list;
	}
	
	/**
	 * 获取试题系列
	 * @return
	 * @throws Exception
	 */
	public Integer getSeq() throws Exception{
		String sql="select EXAM_MAIN_ID_SEQ.nextval SEQ from dual";
		Map map = this.uniqueQuery(sql, new Object[]{});
		Integer seq =(Integer) map.get("SEQ");
		return seq;
	}

}
